const pool = require("./db.js");

class NoticeDao {
    async addNotice(title, content, noticeType, ifEmergency, childNeedExamine, groupPeopleID, examine_type, fId) {
        console.log('在NoticeDao的addNotice里:title:'+title,'content:'+content,'noticeType:'+noticeType,'ifEmergency:'+ifEmergency,'childNeedExamine:'+childNeedExamine,'groupPeopleID:'+groupPeopleID,'examine_type:'+examine_type,'fId:'+fId);
        // 插入通知
        const result = await pool.query(
            "INSERT INTO Notices (notice_title, notice_content, notice_type, notice_ifEmergency, notice_childNeedExamine, group_people_id, examine_type, publish_time, notice_fID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
            [title, content, noticeType, ifEmergency, childNeedExamine, groupPeopleID, examine_type, new Date(), fId]
        );
        const insertedId = result[0].insertId;
        // 返回结果，可以根据需要返回更多信息
        return {
            notice_id: insertedId,
        };
    }

    async deleteNoticesByFId(fId) {
        // 删除 Notices 表中的记录
        const [rows, fields] = await pool.execute("DELETE FROM Notices WHERE notice_fID = ?", [fId]); 
        return rows.affectedRows;
    }

    async deleteNotice(id) {
        // 删除 Notices 表中的记录
        const [rows, fields] = await pool.execute("DELETE FROM Notices WHERE notice_id = ?", [id]); 
        return rows.affectedRows;
    }

    async deleteNoticesByGroupPeopleId(groupPeopleId) {
        // 删除 Notices 表中的记录
        const [rows, fields] = await pool.execute("DELETE FROM Notices WHERE group_people_id = ?", [groupPeopleId]); 
        return rows.affectedRows;
    }

    async selectNoticesByPage(noticeTitle, start, rows, groupPeopleId) {
        console.log("在NoticeDao的selectNoticesByPage里:noticeTitle:"+noticeTitle,"start:"+ start,"rows:"+ rows, "groupPeopleId:"+groupPeopleId);
        let sql1 = "SELECT DISTINCT * FROM Notices where group_people_id=? ";
        let sql2 = "SELECT COUNT(1) AS rowCount FROM Notices where group_people_id=? ";
        let wheresql = "";
    
        if (noticeTitle) {
            wheresql += " and notice_title LIKE '%" + noticeTitle + "%'";
        }
    
        sql1 += wheresql + ` LIMIT ${start}, ${rows}`;
        sql2 += wheresql;
    
        const [rows1,fields1] = await pool.execute(sql1, [groupPeopleId]);
        const [rows2,fields2] = await pool.execute(sql2, [groupPeopleId]);
    
        const rowCount = rows2[0]['rowCount'];
        const pageCount = Math.ceil(rowCount / rows);
    
        return { data: rows1, rowCount:rowCount, pageCount:pageCount };
    }

    async selectNeedExamineNoticesByPage(start, rows, groupId) {
        console.log(start, rows, groupId);
        let sql1 = 
        `SELECT DISTINCT notices.* 
        FROM Notices 
        join groups_people on notices.group_people_id = groups_people.group_people_id 
        join readed on notices.notice_fID = readed.notice_id
        where groups_people.group_id=? AND groups_people.type=1 AND notices.examine_type!=2 AND readed.readed_ifReaded = 0 `;
        let sql2 = "SELECT COUNT(1) AS rowCount FROM Notices join groups_people on notices.group_people_id = groups_people.group_people_id where groups_people.group_id=? AND groups_people.type=1 AND notices.examine_type!=2 ";
        let wheresql = "";

    
        sql1 += wheresql + ` LIMIT ${start}, ${rows}`;
    
        const [rows1,fields1] = await pool.execute(sql1, [groupId]);
        const [rows2,fields2] = await pool.execute(sql2, [groupId]);
    
        const rowCount = rows2[0]['rowCount'];
        const pageCount = Math.ceil(rowCount / rows);
    
        return { data: rows1, rowCount:rowCount, pageCount:pageCount };
    }

    async selectNoticesByFIdAndGpId(fId, gpId){
        const [rows,fields] = await pool.execute("select DISTINCT * from notices where notice_fID=? and group_people_id=?", [fId, gpId]);
        return rows;
    }

    async selectNoticesByGroupPeopleId(groupPeopleId) {
        const [rows,fields] = await pool.execute("select DISTINCT * from notices where group_people_id=?", [groupPeopleId]);
        return rows;
    }

    async selectNoticesById(id){
        const [rows,fields] = await pool.execute("select DISTINCT * from notices where notice_id=?", [id]);
        return rows[0];
    }

    async updateNotice(notice_id, notice_title, notice_content, notice_ifEmergency, notice_childNeedExamine, examine_type, examine_user_id, reject_reason){
        const [rows,fields] = await pool.execute(
            "UPDATE Notices SET notice_title=?, notice_content=?, notice_ifEmergency=?, notice_childNeedExamine=?, examine_type=?, examine_user_id=?, reject_reason=?, publish_time=? WHERE notice_id=?",
            [notice_title, notice_content, notice_ifEmergency, notice_childNeedExamine, examine_type, examine_user_id, reject_reason, new Date(), notice_id]
        )
        return rows;
    }
}

module.exports = new NoticeDao();